package database

import (
	"embed"
	"fmt"
	"io/fs"
	"sort"
	"strings"
	"time"

	"gorm.io/gorm"
)

//go:embed migrations/*.sql
var migrationsFS embed.FS

// Migration 迁移记录
type Migration struct {
	ID        uint      `gorm:"primarykey"`
	Version   string    `gorm:"type:varchar(50);uniqueIndex;not null"` // 版本号，如 "001", "002"
	Name      string    `gorm:"type:varchar(255);not null"`            // 迁移名称
	AppliedAt time.Time `gorm:"not null"`                              // 应用时间
}

func (Migration) TableName() string {
	return "schema_migrations"
}

// MigrationFile 迁移文件结构
type MigrationFile struct {
	Version string
	Name    string
	UpSQL   string // 执行迁移的 SQL
	DownSQL string // 回滚的 SQL（可选）
}

// RunMigrations 执行所有待执行的迁移
func RunMigrations() error {
	db := GetDB()
	if db == nil {
		return fmt.Errorf("数据库未初始化")
	}

	// 创建迁移记录表
	if err := db.AutoMigrate(&Migration{}); err != nil {
		return fmt.Errorf("创建迁移记录表失败: %w", err)
	}

	// 读取所有迁移文件
	migrations, err := loadMigrations()
	if err != nil {
		return fmt.Errorf("加载迁移文件失败: %w", err)
	}

	if len(migrations) == 0 {
		return nil
	}

	// 获取已执行的迁移版本
	appliedVersions, err := getAppliedVersions(db)
	if err != nil {
		return fmt.Errorf("获取已执行迁移失败: %w", err)
	}

	// 过滤出待执行的迁移
	pendingMigrations := filterPendingMigrations(migrations, appliedVersions)

	if len(pendingMigrations) == 0 {
		return nil
	}

	// 按版本号排序
	sort.Slice(pendingMigrations, func(i, j int) bool {
		return pendingMigrations[i].Version < pendingMigrations[j].Version
	})

	// 执行待执行的迁移
	for _, migration := range pendingMigrations {
		if err := executeMigration(db, migration); err != nil {
			return fmt.Errorf("执行迁移 %s 失败: %w", migration.Version, err)
		}
	}

	return nil
}

// RollbackMigration 回滚指定版本的迁移
func RollbackMigration(version string) error {
	db := GetDB()
	if db == nil {
		return fmt.Errorf("数据库未初始化")
	}

	migrations, err := loadMigrations()
	if err != nil {
		return fmt.Errorf("加载迁移文件失败: %w", err)
	}

	var migration *MigrationFile
	for _, m := range migrations {
		if m.Version == version {
			migration = m
			break
		}
	}

	if migration == nil {
		return fmt.Errorf("未找到版本 %s 的迁移", version)
	}

	if migration.DownSQL == "" {
		return fmt.Errorf("迁移 %s 没有回滚 SQL", version)
	}

	// 执行回滚 SQL
	if err := db.Exec(migration.DownSQL).Error; err != nil {
		return fmt.Errorf("回滚迁移失败: %w", err)
	}

	// 删除迁移记录
	if err := db.Where("version = ?", version).Delete(&Migration{}).Error; err != nil {
		return fmt.Errorf("删除迁移记录失败: %w", err)
	}

	return nil
}

// GetMigrationStatus 获取迁移状态
func GetMigrationStatus() ([]MigrationInfo, error) {
	db := GetDB()
	if db == nil {
		return nil, fmt.Errorf("数据库未初始化")
	}

	migrations, err := loadMigrations()
	if err != nil {
		return nil, fmt.Errorf("加载迁移文件失败: %w", err)
	}

	appliedVersions, err := getAppliedVersions(db)
	if err != nil {
		return nil, fmt.Errorf("获取已执行迁移失败: %w", err)
	}

	var status []MigrationInfo
	for _, migration := range migrations {
		appliedAt := ""
		if appliedVersions[migration.Version] {
			var record Migration
			db.Where("version = ?", migration.Version).First(&record)
			appliedAt = record.AppliedAt.Format(time.RFC3339)
		}

		status = append(status, MigrationInfo{
			Version:   migration.Version,
			Name:      migration.Name,
			Applied:   appliedVersions[migration.Version],
			AppliedAt: appliedAt,
		})
	}

	// 按版本号排序
	sort.Slice(status, func(i, j int) bool {
		return status[i].Version < status[j].Version
	})

	return status, nil
}

// MigrationInfo 迁移信息
type MigrationInfo struct {
	Version   string `json:"version"`
	Name      string `json:"name"`
	Applied   bool   `json:"applied"`
	AppliedAt string `json:"applied_at,omitempty"`
}

// loadMigrations 加载所有迁移文件
func loadMigrations() ([]*MigrationFile, error) {
	entries, err := fs.ReadDir(migrationsFS, "migrations")
	if err != nil {
		// 如果 migrations 目录不存在，返回空列表（不报错）
		return []*MigrationFile{}, nil
	}

	var migrations []*MigrationFile
	for _, entry := range entries {
		if entry.IsDir() || !strings.HasSuffix(entry.Name(), ".sql") {
			continue
		}

		// 跳过占位文件
		if entry.Name() == ".gitkeep.sql" {
			continue
		}

		// 解析文件名：001_create_users_table.sql
		filename := strings.TrimSuffix(entry.Name(), ".sql")
		parts := strings.SplitN(filename, "_", 2)
		if len(parts) < 2 {
			continue
		}

		version := parts[0]
		name := parts[1]

		// 读取文件内容
		content, err := migrationsFS.ReadFile("migrations/" + entry.Name())
		if err != nil {
			continue
		}

		sqlContent := string(content)
		upSQL, downSQL := parseMigrationSQL(sqlContent)

		migrations = append(migrations, &MigrationFile{
			Version: version,
			Name:    name,
			UpSQL:   upSQL,
			DownSQL: downSQL,
		})
	}

	return migrations, nil
}

// parseMigrationSQL 解析迁移 SQL（支持 -- UP 和 -- DOWN 分隔）
func parseMigrationSQL(content string) (upSQL string, downSQL string) {
	lines := strings.Split(content, "\n")
	var upLines, downLines []string
	inUp := true

	for _, line := range lines {
		if strings.HasPrefix(strings.TrimSpace(line), "-- UP") {
			inUp = true
			continue
		}
		if strings.HasPrefix(strings.TrimSpace(line), "-- DOWN") {
			inUp = false
			continue
		}
		if inUp {
			upLines = append(upLines, line)
		} else {
			downLines = append(downLines, line)
		}
	}

	upSQL = strings.TrimSpace(strings.Join(upLines, "\n"))
	downSQL = strings.TrimSpace(strings.Join(downLines, "\n"))
	return
}

// getAppliedVersions 获取已应用的迁移版本
func getAppliedVersions(db *gorm.DB) (map[string]bool, error) {
	var records []Migration
	if err := db.Find(&records).Error; err != nil {
		return nil, err
	}

	applied := make(map[string]bool)
	for _, record := range records {
		applied[record.Version] = true
	}

	return applied, nil
}

// filterPendingMigrations 过滤出待执行的迁移
func filterPendingMigrations(migrations []*MigrationFile, appliedVersions map[string]bool) []*MigrationFile {
	var pending []*MigrationFile
	for _, migration := range migrations {
		if !appliedVersions[migration.Version] {
			pending = append(pending, migration)
		}
	}
	return pending
}

// executeMigration 执行单个迁移
func executeMigration(db *gorm.DB, migration *MigrationFile) error {
	// 开始事务
	return db.Transaction(func(tx *gorm.DB) error {
		// 执行迁移 SQL
		if migration.UpSQL != "" {
			if err := tx.Exec(migration.UpSQL).Error; err != nil {
				return fmt.Errorf("执行 SQL 失败: %w", err)
			}
		}

		// 记录迁移
		record := Migration{
			Version:   migration.Version,
			Name:      migration.Name,
			AppliedAt: time.Now(),
		}
		if err := tx.Create(&record).Error; err != nil {
			return fmt.Errorf("记录迁移失败: %w", err)
		}

		return nil
	})
}
